/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package functionalEnrichment;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import utils.FileUtils;
import utils.Handle;

/**
 *
 * @author jamie
 */
public class MakeEnrichmentTable {

    private File inputFolder = new File("/Users/jamie/Dropbox/monkeys/GO/DAVID/");

    public void makeTables() {
        Handle handle = Handle.getConnectedHandle("monkeys");
        String sql;
        sql = "drop table if exists enrichment";
        handle.runSQLupdate(sql);
        sql = "create table enrichment (id int not null primary key, clusterId int(2) not null, term varchar(255) not null, noOfGenes int(3) not null, percent double not null, " +
                "foldEnrichment double not null, pValue double not null, fdr double not null)";
        handle.runSQLupdate(sql);
        sql = "create index clusterId_index on enrichment(clusterId)";
        handle.runSQLupdate(sql);

        sql = "drop table if exists enrichmentDetails";
        handle.runSQLupdate(sql);
        sql = "create table enrichmentDetails (id int not null primary key auto_increment, enrichmentId int not null, gId int not null)";
        handle.runSQLupdate(sql);
        sql = "create index enrichmentId_index on enrichmentDetails(enrichmentId)";
        handle.runSQLupdate(sql);
        sql = "create index gId_index on enrichmentDetails(gId)";
        handle.runSQLupdate(sql);
        handle.closeConnection();
    }

    public void fillTables() throws IOException {
        int entryId = 1;
        Handle handle = Handle.getConnectedHandle("monkeys");
        String sql;
        for (File clusterFolder : inputFolder.listFiles()) {
            if (clusterFolder.getName().startsWith("cluster")) {
                int clusterId = Integer.parseInt(clusterFolder.getName().substring(7));
                File dataFile = new File(clusterFolder, "chart.txt");
                BufferedReader reader = FileUtils.getBufferedReader(dataFile);
                String line;
                boolean first = true;
                while ((line = reader.readLine()) != null) {
                    if (!first) {
                        String[] split = line.split("\t");
                        if (split.length > 1) {
                            String term = split[1];
                            term = term.replace("'", "");
                            double percent = Double.parseDouble(split[3]);
                            double pValue = Double.parseDouble(split[4]);
                            List<Integer> gIds = getGIds(split[5]);
                            double foldEnrichment = Double.parseDouble(split[9]);
                            double fdr = Double.parseDouble(split[11]);
                            
                            sql = "insert into enrichment values(" + entryId + ", " + clusterId + ", '" + term + "', " + gIds.size() + ", " + percent + ", " + foldEnrichment + ", " + pValue + ", " + fdr + ")";
                            handle.runSQLupdate(sql);
                            for (int gId : gIds) {
                                sql = "insert into enrichmentDetails values (0," + entryId + ", " + gId + ")";
                                handle.runSQLupdate(sql);
                            }
                            entryId++;
                        }
                    } else {
                        first = false;
                    }
                }
                reader.close();

            }
        }
        handle.closeConnection();
    }

    private List<Integer> getGIds(String csv) {
        String[] split = csv.split(",");
        List<Integer> gIds = new ArrayList<Integer>();
        for (String s : split) {
            try {
                gIds.add(Integer.parseInt(s.trim()));
            } catch (NumberFormatException nfe) {
                nfe.printStackTrace();
            }
        }
        return gIds;

    }
}
